--    Program: 		CAPAREXT_MEMO_LINE.sql
--    Description:  Extract AR Memo Line information  
--    Revision:     1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_processed_count_r		number;
   l_rejected_count_r 		number;
   l_err_msg_r				varchar2(2000);
  
  
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_utl_file_r       		utl_file.file_type;
   l_filename_r       		varchar2(255);
   l_pathname_r       		varchar2(100);
   
   l_ngo_sob				varchar2(20);
   l_ngo_sob_id             number;
   
  cursor cur_b (p_set_of_books_id number) is
	select amlal.*
	from   AR_MEMO_LINES_ALL_B amlal,
	       hr_organization_information hoi
    where  hoi.org_information_context = 'Operating Unit Information'
    and    hoi.org_information3 = to_char(p_set_of_books_id)
    and    hoi.organization_id = amlal.org_id;
		
  cursor cur_r (p_org_id number , p_memo_line_id number) is
	select * 
	from AR_MEMO_LINES_ALL_TL
    where org_id = p_org_id  
    and memo_line_id = p_memo_line_id;    
    
begin
   l_pathname := '/tmp';
   l_filename := 'CAPAR_MEMO_LINE_B.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_pathname_r := '/tmp';
   l_filename_r := 'CAPAR_MEMO_LINE_TL.dat';
   l_utl_file_r := utl_file.fopen(l_pathname_r, l_filename_r, 'w');

   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_processed_count_r := 0;
   l_rejected_count_r  := 0;  
   
   l_ngo_sob := '&1';
   
    begin
      select set_of_books_id
      into l_ngo_sob_id
      from gl_sets_of_books
      where upper(short_name) = upper(l_ngo_sob);
    exception
      when others then
        dbms_output.put_line(l_ngo_sob||': set of books name not found');
    end;
    
    for cur in cur_b(l_ngo_sob_id) loop   	
     
       -- output data
       utl_file.put_line( l_utl_file, 
         cur.MEMO_LINE_ID|| chr(9) ||    
         cur.ORG_ID || chr(9) || 
         cur.set_of_books_id || chr(9) || 
         cur.line_type || chr(9) ||         
         cur.START_DATE  || chr(9) ||   
         cur.LAST_UPDATE_DATE || chr(9) || 
         cur.last_updated_by || chr(9) ||    
         cur.created_by || chr(9) || 
         cur.CREATION_DATE || chr(9) ||
  		 cur.GL_ID_REV || chr(9) ||
  		 cur.TAX_CODE  || chr(9) ||
  		 cur.UOM_CODE  || chr(9) ||
  		 cur.UNIT_STD_PRICE   || chr(9) ||
  		 cur.INVOICING_RULE_ID   || chr(9) ||
  		 cur.ACCOUNTING_RULE_ID    || chr(9) ||
  		 cur.END_DATE  || chr(9) ||
  		 cur.ATTRIBUTE_CATEGORY   || chr(9) ||
  		 cur.ATTRIBUTE1 || chr(9) ||
  		 cur.ATTRIBUTE2 || chr(9) ||
  		 cur.ATTRIBUTE3 || chr(9) ||
  		 cur.ATTRIBUTE4 || chr(9) ||
  		 cur.ATTRIBUTE5 || chr(9) ||
  		 cur.ATTRIBUTE6 || chr(9) ||
  		 cur.ATTRIBUTE7 || chr(9) ||
  		 cur.ATTRIBUTE8 || chr(9) ||
  		 cur.ATTRIBUTE9 || chr(9) ||
  		 cur.ATTRIBUTE10 || chr(9) ||
         cur.ATTRIBUTE11 || chr(9) ||
         cur.ATTRIBUTE12 || chr(9) ||
         cur.ATTRIBUTE13 || chr(9) ||
         cur.ATTRIBUTE14 || chr(9) ||
         cur.ATTRIBUTE15 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE_CATEGORY  || chr(9) ||
         cur.GLOBAL_ATTRIBUTE1 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE2 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE3 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE4 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE5 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE6 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE7 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE8 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE9 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE10 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE11 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE12 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE13 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE14 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE15 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE16 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE17 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE18 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE19 || chr(9) ||
         cur.GLOBAL_ATTRIBUTE20 || chr(9) ||
         cur.LAST_UPDATE_LOGIN         
	   );
	    
	   l_processed_count := l_processed_count + 1;  
    
	   for cur1 in cur_r (cur.ORG_ID,cur.MEMO_LINE_ID) loop    
         
         utl_file.put_line( l_utl_file_r, 
           cur1.MEMO_LINE_ID|| chr(9) || 
           cur1.ORG_ID|| chr(9) ||
           cur1.NAME|| chr(9) ||
           cur1.DESCRIPTION|| chr(9) ||
           cur1.LANGUAGE || chr(9) ||
           cur1.SOURCE_LANG || chr(9) ||
           cur1.LAST_UPDATE_DATE || chr(9) ||
           cur1.CREATION_DATE || chr(9) ||
           cur1.CREATED_BY || chr(9) ||
           cur1.LAST_UPDATED_BY || chr(9) ||
           cur1.LAST_UPDATE_LOGIN
         );
      
         l_processed_count_r := l_processed_count_r + 1;  
       end loop;                
   end loop;
  
   l_err_msg := '';
   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Memo Line B not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPAREXT_MEMO_LINE_B', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   l_err_msg := '';
   if l_processed_count_r = 0 then
      l_rejected_count_r := l_rejected_count_r + 1;
      l_err_msg := l_ngo_sob || ': Memo Line TL not found';  
   end if;    
     
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ( 'CAPAREXT_MEMO_LINE_TL', sysdate, l_processed_count_r, l_rejected_count_r, l_processed_count_r-l_rejected_count_r, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
   utl_file.fclose(l_utl_file_r);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
